// @ts-nocheck
const Service = require('egg').Service;
const dayjs = require('dayjs');

class BillService extends Service {
    async findAll(opt) {
        try {
            const { startTime, endTime, transactionType, trader, type, paymentMethod, payType, id, sorter } = opt;
            const pageNum = this.ctx.query.current || 1; // 当前页码，默认为1
            const pageSize = this.ctx.query.pageSize || 20; // 每页显示的记录数，默认为10
            const offset = (pageNum - 1) * pageSize;
            const limit = pageSize;

            let sql = `
             SELECT a.*, b.value as transactionTypeValue, DATE_FORMAT(a.payTime, '%Y-%m-%d %H:%i:%s') AS payTime FROM bill AS a LEFT JOIN transactionconfig AS B ON a.transactionType = b.key WHERE 1 = 1
            `;
            if (id) {
                sql += `AND a.id = '${id}' `;
            }
            if (startTime && endTime) {
                sql += `AND a.payTime >= '${startTime}' AND a.payTime <= '${endTime}' `;
            }
            if (transactionType) {
                sql += `AND a.transactionType = ${transactionType} `;
            }
            if (trader) {
                sql += `AND a.trader = '${trader}' `;
            }
            if (type) {
                sql += `AND a.type = ${type} `;
            }
            if (paymentMethod) {
                sql += `AND a.paymentMethod = ${paymentMethod} `;
            }
            if (payType) {
                sql += `AND a.payType = ${payType} `;
            }
            if (offset && limit) {
                sql += `LIMIT ${limit} OFFSET ${offset} `;
            }
            if (sorter && sorter.payTime) {
                if (sorter.payTime === 'ascend') {
                    sql += `ORDER BY payTime ASC `;
                } else if (sorter.payTime === 'descend') {
                    sql += `ORDER BY payTime DESC `;
                }
            } else {
                sql += `ORDER BY payTime DESC `;
            }
            const data = await this.app.mysql.query(sql);
            return data;
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async insert(opt) {
        try {
            const data = await this.app.mysql.insert('bill', opt);
            return data;
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async querySum(opt) {
        try {
            const { startTime, endTime, transactionType, trader, type, paymentMethod, payType, id, sorter } = opt;
            const pageNum = this.ctx.query.current || 1; // 当前页码，默认为1
            const pageSize = this.ctx.query.pageSize || 20; // 每页显示的记录数，默认为10
            const offset = (pageNum - 1) * pageSize;
            const limit = pageSize;

            let sql = `
            SELECT * FROM bill WHERE 1 = 1
            `;
            if (id) {
                sql += `AND id = '${id}' `;
            }
            if (startTime && endTime) {
                sql += `AND payTime >= '${startTime}' AND payTime <= '${endTime}' `;
            }
            if (transactionType) {
                sql += `AND transactionType = ${transactionType} `;
            }
            if (trader) {
                sql += `AND trader = '${trader}' `;
            }
            if (type && type.length) {
                sql += `AND type = ${type} `;
            }
            if (paymentMethod) {
                sql += `AND paymentMethod = ${paymentMethod} `;
            }
            if (payType && payType.length) {
                sql += `AND payType = ${payType} `;
            }
            if (offset && limit) {
                sql += `LIMIT ${limit} OFFSET ${offset} `;
            }
            let allSql = `SELECT SUM(CASE WHEN payType = 1 AND type = 1 THEN money ELSE 0 END) AS alipay_income_total, 
                SUM(CASE WHEN payType = 1 AND type = 2 THEN money ELSE 0 END) AS alipay_expenditure_total,
                SUM(CASE WHEN payType = 2 AND type = 1 THEN money ELSE 0 END) AS wechat_income_total,
                SUM(CASE WHEN payType = 2 AND type = 2 THEN money ELSE 0 END) AS wechat_expenditure_total,
                SUM(CASE WHEN type = 3 THEN money ELSE 0 END) AS not_counting_total,
                SUM(CASE WHEN type = 1 THEN money ELSE 0 END) AS inCome_total,
                SUM(CASE WHEN type = 2 THEN money ELSE 0 END) AS expenditure_total FROM (${sql}) AS subquery`
            console.log(allSql);
            const data = await this.app.mysql.query(allSql);
            if (data.length > 0) {
                return data[0];
            }
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async delete(opt) {
        try {
            const { startTime, endTime, transactionType, trader, type, paymentMethod, payType, ids } = opt;

            let sql = `
            DELETE FROM bill WHERE 1 = 1
            `;
            if (ids && ids.length) {
                console.log(ids);
                // const placeholders = ids.join(',');
                let str = '';
                ids.forEach((id, index) => {
                    if (index !== ids.length - 1) {
                        str += `'${id}',`;
                    } else {
                        str += `'${id}'`
                    }

                });
                console.log(str);
                sql += `AND id IN (${str}) `;
            }
            if (startTime && endTime) {
                sql += `AND payTime >= '${startTime}' AND payTime <= '${endTime}' `;
            }
            if (transactionType) {
                sql += `AND transactionType = ${transactionType} `;
            }
            if (trader) {
                sql += `AND trader = '${trader}' `;
            }
            if (type) {
                sql += `AND type = ${type} `;
            }
            if (paymentMethod) {
                sql += `AND paymentMethod = ${paymentMethod} `;
            }
            if (payType) {
                sql += `AND payType = ${payType} `;
            }
            console.log(sql);
            const data = await this.app.mysql.query(sql);
            return data;
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async getBillAllByDay(opt) {
        try {
            let { month, startTime, endTime, transactionType, trader, type, paymentMethod, payType, id } = opt;
            const pageNum = this.ctx.query.current || 1; // 当前页码，默认为1
            const pageSize = this.ctx.query.pageSize || 20; // 每页显示的记录数，默认为10
            const offset = (pageNum - 1) * pageSize;
            const limit = pageSize;
            let sql = `SELECT DATE_FORMAT(payTime, '%Y-%m-%d') AS payDay, count(*) as count, SUM(CASE WHEN payType = 1 AND type = 1 THEN money ELSE 0 END) AS alipay_income_total, 
            SUM(CASE WHEN payType = 1 AND type = 2 THEN money ELSE 0 END) AS alipay_expenditure_total,
            SUM(CASE WHEN payType = 2 AND type = 1 THEN money ELSE 0 END) AS wechat_income_total,
            SUM(CASE WHEN payType = 2 AND type = 2 THEN money ELSE 0 END) AS wechat_expenditure_total,
            SUM(CASE WHEN type = 3 THEN money ELSE 0 END) AS not_counting_total,
            SUM(CASE WHEN type = 1 THEN money ELSE 0 END) AS inCome_total,
            SUM(CASE WHEN type = 2 THEN money ELSE 0 END) AS expenditure_total, GROUP_CONCAT(JSON_OBJECT('id', id, 'payTime', DATE_FORMAT(payTime,'%Y-%m-%d %H:%i:%s'), 'transactionType', transactionType, 'trader', trader, 'tradingAccount', tradingAccount, 'notes', notes, 'type', type, 'money', money, 'paymentMethod', paymentMethod, 'transactionOrderNumber', transactionOrderNumber, 'merchantOrderNumber', merchantOrderNumber, 'userId', userId, 'payType', payType, 'isDelete', isDelete)) AS children
        FROM bill WHERE true `;
            if (!(startTime && endTime) && month) {
                sql += `AND payTime >= STR_TO_DATE(CONCAT('${month}', '-01'), '%Y-%m-%d') 
            AND payTime < DATE_ADD(STR_TO_DATE(CONCAT('${month}', '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH) `;
            }
            if (startTime && endTime) {
                sql += `AND payTime >= '${startTime}' AND payTime <= '${endTime}' `;
            }
            if (id) {
                sql += `AND id = '${id}' `;
            }
            if (transactionType) {
                sql += `AND transactionType = ${transactionType} `;
            }
            if (trader) {
                sql += `AND trader = '${trader}' `;
            }
            if (type && type.length) {
                sql += `AND type IN (${type.join(',')}) `;
            }
            if (paymentMethod) {
                sql += `AND paymentMethod = ${paymentMethod} `;
            }
            if (payType && payType.length) {

                sql += `AND payType IN (${payType.join(',')}) `;
            }
            if (offset && limit) {
                sql += `LIMIT ${limit} OFFSET ${offset} `;
            }
            sql += `GROUP BY payDay ORDER BY payDay DESC`;
            console.log(sql);
            // await this.app.mysql.query(`SET SESSION group_concat_max_len = 102400000`); //临时设置 group_concat_max_len 为 100m，解决数据查询不全
            const data = await this.app.mysql.query(sql);
            return data;
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async getExpenditureTrend(opt) {
        try {
            const { startTime, endTime, type } = opt;
            if (startTime && endTime && type) {
                let sql = `SELECT DATE_FORMAT(payTime, '%Y-%m-%d') AS payDay, SUM(money) AS money FROM bill WHERE payTime >= '${startTime}' AND payTime <= '${endTime}' AND type = ${type} GROUP BY payDay ORDER BY payDay ASC`;
                const data = await this.app.mysql.query(sql);
                return data;
            }
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async getClassificationOfExpenditure(opt) {
        try {
            const { startTime, endTime, type } = opt;
            if (startTime && endTime && type) {
                const { startTime, endTime, type } = opt;
                let sql = `SELECT SUM(money) AS money, transactionType, COUNT(*) AS count FROM bill WHERE payTime >= '${startTime}' AND payTime <= '${endTime}' AND type = ${type} GROUP BY transactionType HAVING SUM(money) > 0 ORDER BY money DESC`;
                const data = await this.app.mysql.query(sql);
                return data;
            }
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async getExpenditureRank(opt) {
        try {
            const { startTime, endTime, type } = opt;
            if (startTime && endTime && type) {
                let sql = `SELECT id, money, notes, transactionType FROM bill WHERE payTime >= '${startTime}' AND payTime <= '${endTime}' AND type = ${type} ORDER BY money DESC`;
                const data = await this.app.mysql.query(sql);
                return data;
            }
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async getSumMoneyByType(opt) {
        try {
            const { startTime, endTime, type } = opt;
            if (startTime && endTime && type) {
                let sql = `SELECT SUM(money) AS sumMoney FROM bill WHERE payTime >= '${startTime}' AND payTime <= '${endTime}' AND type = ${type}`;
                const data = await this.app.mysql.query(sql);
                return data[0];
            }
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async getExpendSumMoney(opt) {
        try {
            const { startTime, endTime } = opt;
            if (startTime && endTime) {
                let sql = `SELECT SUM(money) AS expendSumMoney FROM bill WHERE payTime >= '${startTime}' AND payTime <= '${endTime}' AND type = 2`;
                const data = await this.app.mysql.query(sql);
                return data[0];
            }
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async getDataByTransactionType(opt) {
        try {
            const { startTime, endTime, type, transactionType } = opt;
            if (startTime && endTime && type && transactionType) {
                const { startTime, endTime, type } = opt;
                let sql = `SELECT * FROM bill WHERE payTime >= '${startTime}' AND payTime <= '${endTime}' AND type = ${type} AND transactionType = ${transactionType} ORDER BY payTime DESC`;
                const data = await this.app.mysql.query(sql);
                return data;
            }
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
    async getSumDataByTransactionType(opt) {
        try {
            const { startTime, endTime, type, transactionType } = opt;
            if (startTime && endTime && type && transactionType) {
                const { startTime, endTime, type } = opt;
                let sql = `SELECT SUM(money) as sum FROM bill WHERE payTime >= '${startTime}' AND payTime <= '${endTime}' AND type = ${type} AND transactionType = ${transactionType}`;
                const data = await this.app.mysql.query(sql);
                return data[0];
            }
        } catch (err) {
            this.ctx.throw(this.app.config.code.ERROR, err);
        }
    }
}

module.exports = BillService;